﻿/***************************************************************
 * 开发人员：网魂小兵@http://www.mozlite.com
 * 
 * SQL Server脚本文件数据库提供者。
 * 
 * 最后修改：2011-10-25
 * ************************************************************/
namespace Mozlite.Data.SqlDataProvider
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.SqlClient;
    using System.Text.RegularExpressions;
    using System.Data.Common;
    using System.Data;
    using System.IO;
    using System.Web.Hosting;

    /// <summary>
    /// 脚本文件数据库提供者。
    /// </summary>
    public class SqlScriptDataProvider : ScriptDataProvider
    {
        /// <summary>
        /// 初始化类<see cref="T:Mozlite.Data.SqlDataProvider.SqlScriptDataProvider"/>。
        /// </summary>
        public SqlScriptDataProvider() : base(SqlClientFactory.Instance) { }

        /// <summary>
        /// 执行脚本字符串。
        /// </summary>
        /// <param name="sql">数据库字符串。</param>
        /// <returns>返回错误字符串。</returns>
        protected override string ExecuteScript(string script)
        {
            string exceptions = null;
            string[] arrSQL = scriptDelimiterRegex.Split(script);
            foreach (string SQL in arrSQL)
            {
                string s = SQL;
                if (!String.IsNullOrEmpty(s.Trim()))
                {
                    //script dynamic substitution
                    s = EscapeScript(s);
                    try
                    {
                        //Logs.Trace("Executing SQL Script " + s);
                        ExecuteNonQuery(s);
                    }
                    catch (SqlException objException)
                    {
                        //Logs.Debug(objException);

                        exceptions += objException + Environment.NewLine + Environment.NewLine + s + Environment.NewLine + Environment.NewLine;
                    }
                }
            }
            return exceptions;
        }

        /// <summary>
        /// 使用数据库事务执行脚本字符串。
        /// </summary>
        /// <param name="sql">数据库字符串。</param>
        /// <returns>返回错误字符串。</returns>
        protected override string ExecuteTransactionScript(string script)
        {
            string exception = null;
            DbTransaction transaction = GetTransaction();
            try
            {
                exception += ExecuteScript(script, transaction);
                if (String.IsNullOrEmpty(exception))
                {
                    //No exceptions so go ahead and commit
                    transaction.CommitTransaction();
                }
                else
                {
                    //Found exceptions, so rollback db
                    transaction.RollbackTransaction();
                    exception += "SQL Execution failed.  Database was rolled back" + Environment.NewLine + Environment.NewLine + script + Environment.NewLine + Environment.NewLine;
                }
            }
            finally
            {
                if (transaction != null && transaction.Connection != null)
                {
                    transaction.Connection.Close();
                }
            }
            return exception;
        }

        /// <summary>
        /// 获取数据库引擎版本。
        /// </summary>
        /// <returns>返回版本实例对象。</returns>
        public override Version GetDatabaseEngineVersion()
        {
            string version = "0.0";
            IDataReader reader = null;
            try
            {
                using (reader = GetDatabaseServer())
                {
                    if (reader.Read())
                        version = reader.GetString("Version");
                    reader.Close();
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return new Version(version);
        }

        /// <summary>
        /// 获取数据库服务器读取器。
        /// </summary>
        /// <returns>返回数据库读取实例对象。</returns>
        public override System.Data.IDataReader GetDatabaseServer()
        {
            return ExecuteReader(AddProfix("GetDatabaseServer"), null, System.Data.CommandType.StoredProcedure);
        }

        /// <summary>
        /// 获取数据库服务器版本读取器。
        /// </summary>
        /// <returns>返回数据库读取版本实例对象。</returns>
        public override System.Data.IDataReader GetDatabaseVersion()
        {
            return ExecuteReader(AddProfix("GetDatabaseVersion"), null, System.Data.CommandType.StoredProcedure);
        }

        /// <summary>
        /// 获取版本。
        /// </summary>
        /// <returns>返回版本。</returns>
        public override Version GetVersion()
        {
            Version version = null;
            try
            {
                IDataReader dr = GetDatabaseVersion();
                if (dr.Read())
                {
                    version = new Version(dr.GetInt32("Major"), dr.GetInt32("Minor"), dr.GetInt32("Build"));
                }
            }
            catch (SqlException ex)
            {
                bool noStoredProc = false;
                for (int i = 0; i <= ex.Errors.Count - 1; i++)
                {
                    SqlError sqlError = ex.Errors[i];
                    if (sqlError.Number == 2812 && sqlError.Class == 16) //2812 - 16 means SP could not be found
                    {
                        noStoredProc = true;
                        break;
                    }
                }

                if (!noStoredProc)
                {
                    throw;
                }
            }
            return version;
        }

        private static readonly Regex scriptDelimiterRegex = new Regex("(?<=(?:[^\\w]+|^))GO(?=(?: |\\t)*?(?:\\r?\\n|$))", RegexOptions.IgnoreCase | RegexOptions.Compiled | RegexOptions.Multiline);

        /// <summary>
        /// 执行脚本。
        /// </summary>
        /// <param name="script">脚本字符串。</param>
        /// <param name="transaction">数据库事务实例对象。</param>
        /// <returns>返回错误字符串。</returns>
        public override string ExecuteScript(string script, DbTransaction transaction)
        {
            string exceptions = null;
            string[] arrSQL = scriptDelimiterRegex.Split(script);
            bool ignoreErrors;
            foreach (string sql in arrSQL)
            {
                string s = sql;
                if (!string.IsNullOrEmpty(s.Trim()))
                {
                    //script dynamic substitution
                    s = EscapeScript(s);
                    ignoreErrors = false;
                    if (s.Trim().StartsWith("{IgnoreError}"))
                    {
                        ignoreErrors = true;
                        s = s.Replace("{IgnoreError}", "");
                    }
                    try
                    {
                        ExecuteNonQuery((SqlTransaction)transaction, s);
                    }
                    catch (SqlException objException)
                    {
                        //Logs.Debug(objException);

                        if (!ignoreErrors)
                        {
                            exceptions += objException + Environment.NewLine + Environment.NewLine + s + Environment.NewLine + Environment.NewLine;
                        }
                    }
                }
            }
            return exceptions;
        }

        /// <summary>
        /// 脚本目录。
        /// </summary>
        /// <returns>返回脚本物理路径。</returns>
        public override string GetScriptPath()
        {
            string path = Settings["providerPath"];
            if (!string.IsNullOrEmpty(path))
            {
                path = HostingEnvironment.MapPath(path);

                if (Directory.Exists(path))
                {
                    if (!CanConnect(Settings.ConnectionString, Settings.DbOwner, Settings.DbProfix))
                    {
                        path = "ERROR: Could not connect to database specified in connectionString for SqlDataProvider";
                    }
                }
                else
                {
                    path = "ERROR: providerPath folder " + path + " specified for SqlDataProvider does not exist on web server";
                }
            }
            else
            {
                path = "ERROR: providerPath folder value not specified in web.config for SqlDataProvider";
            }
            return path;
        }

        private bool CanConnect(string connectionString, string owner, string qualifier)
        {
            bool connectionValid = true;

            try
            {
                if (!string.IsNullOrEmpty(owner))
                    owner += ".";
                if (!string.IsNullOrEmpty(qualifier))
                    qualifier += "_";
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    var dr = ExecuteReader(connection, owner + qualifier + "GetDatabaseVersion", null, CommandType.StoredProcedure);
                    if (dr.Read())
                        dr.Close();
                }
            }
            catch (SqlException ex)
            {
                foreach (SqlError c in ex.Errors)
                {
                    if (!(c.Number == 2812 && c.Class == 16))
                    {
                        connectionValid = false;
                        break;
                    }
                }
            }

            return connectionValid;
        }

        /// <summary>
        /// 测试数据库连接。
        /// </summary>
        /// <param name="builder">为强类型连接字符串生成器提供基类。</param>
        /// <param name="owner">数据库所有者。</param>
        /// <param name="qualifier">数据表前缀。</param>
        /// <returns>返回执行错误信息。</returns>
        public override string TestDatabaseConnection(DbConnectionStringBuilder builder, string owner, string qualifier)
        {
            var sqlBuilder = builder as SqlConnectionStringBuilder;
            string connectionString = null;
            if (sqlBuilder != null)
            {
                connectionString = sqlBuilder.ToString();
                IDataReader dr = null;
                try
                {
                    if (!string.IsNullOrEmpty(owner))
                        owner += ".";
                    if (!string.IsNullOrEmpty(qualifier))
                        qualifier += "_";
                    using(SqlConnection connection = new SqlConnection(connectionString)){
                        dr = ExecuteReader(connection, owner + qualifier + "GetDatabaseVersion", null, CommandType.StoredProcedure);
                    }
                }
                catch (SqlException ex)
                {
                    string message = "ERROR:";
                    bool bError = true;
                    int i;
                    var errorMessages = new StringBuilder();
                    for (i = 0; i <= ex.Errors.Count - 1; i++)
                    {
                        SqlError sqlError = ex.Errors[i];
                        if (sqlError.Number == 2812 && sqlError.Class == 16)
                        {
                            bError = false;
                            break;
                        }
                        else
                        {
                            string filteredMessage = String.Empty;
                            switch (sqlError.Number)
                            {
                                case 17:
                                    filteredMessage = "Sql server does not exist or access denied";
                                    break;
                                case 4060:
                                    filteredMessage = "Invalid Database";
                                    break;
                                case 18456:
                                    filteredMessage = "Sql login failed";
                                    break;
                                case 1205:
                                    filteredMessage = "Sql deadlock victim";
                                    break;
                            }
                            errorMessages.Append("<b>Index #:</b> " + i + "<br/>" + "<b>Source:</b> " + sqlError.Source + "<br/>" + "<b>Class:</b> " + sqlError.Class + "<br/>" + "<b>Number:</b> " +
                                                 sqlError.Number + "<br/>" + "<b>Message:</b> " + filteredMessage + "<br/><br/>");
                        }
                    }
                    if (bError)
                    {
                        connectionString = message + errorMessages;
                    }
                }
                finally
                {
                   if(dr!= null)
                       dr.Close();
                }
            }
            else
            {
                //Invalid DbConnectionStringBuilder
            }
            return connectionString;
        }

        /// <summary>
        /// 更新数据库版本。
        /// </summary>
        /// <param name="major">获取当前<see cref="T:System.Version"/>对象版本号的主要版本号部分的值。</param>
        /// <param name="minor">获取当前<see cref="T:System.Version"/>对象版本号的次要版本号部分的值。</param>
        /// <param name="build">获取当前<see cref="T:System.Version"/>对象版本号的内部版本号部分的值。</param>
        /// <param name="name">名称</param>
        public override void UpdateDatabaseVersion(int major, int minor, int build, string name)
        {
            ExecuteNonQuery(AddProfix("UpdateDatabaseVersion"), new SqlParameter[]{
                new SqlParameter{SqlDbType = SqlDbType.Int, ParameterName = "Major", Value = major},
                new SqlParameter{SqlDbType = SqlDbType.Int, ParameterName = "Minor", Value = minor},
                new SqlParameter{SqlDbType = SqlDbType.Int, ParameterName = "Build", Value = build},
                new SqlParameter{SqlDbType = SqlDbType.NVarChar, ParameterName = "Name", Value = N(name), Size = 50},
            }, CommandType.StoredProcedure);
        }
    }
}
